package net.cellkyborg.server;

import java.io.*;
import java.sql.*;

import net.cellkyborg.RSInstances.*;

import com.mchange.v2.c3p0.DataSources;
import com.mchange.v2.c3p0.PoolConfig;
import com.mchange.v2.c3p0.PooledDataSource;
import javax.sql.DataSource;

public class SQLHandler {

	public static Connection conn = null;
	public static DataSource ds = null;
	public static DataSource dsp = null;
	public static Statement stmt = null;
	public static ResultSet rs;

	public SQLHandler() {
		try {
			PoolConfig config = new PoolConfig();
			config.setAutoCommitOnClose(true);
			config.setInitialPoolSize(3);
			config.setMinPoolSize(1);
			config.setMaxPoolSize(50);
			config.setAcquireRetryAttempts(0);
			config.setAcquireRetryDelay(500);
			config.setCheckoutTimeout(0);
			config.setAcquireIncrement(5);
			config.setAutomaticTestTable("connection_test_table");
			config.setTestConnectionOnCheckin(true);
			config.setIdleConnectionTestPeriod(60);
			config.setMaxIdleTime(0);
			config.setMaxStatementsPerConnection(10);// 100
			config.setBreakAfterAcquireFailure(false);
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			ds = DataSources.unpooledDataSource(
					"jdbc:mysql://localhost/459Server", "474", "");
			dsp = DataSources.pooledDataSource(ds, config);
			conn = dsp.getConnection();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	public int readCharacter(Player user) {
		if (server.playerHandler.updateRunning) {
			user.returnCode = 14;
			misc.println(user.playerName + " refused - update is running !");
			return 3;
		} else if (server.playerHandler.isPlayerOn(user.playerName)) {
			user.returnCode = 5;
			user.disconnected = true;
			user.savefile = false;
			misc.println(user.playerName + " is already online.");
			return 3;
		} else {
			int LoadGame = loadCharacter(user);
			if (LoadGame == 2) { // Wrong password
				user.returnCode = 3;
				return 3;
			} else if (LoadGame == 3) {// wrong username
				user.returnCode = 3;
				return 3;
			}
		}
		return 1;
	}

	public int loadCharacter(Player user) {
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM characters WHERE username='"
					+ user.playerName + "' LIMIT 1");
			if (!rs.next())
				return 3;
			else if (!rs.getString(3).equalsIgnoreCase(user.playerPass))
				return 2;
			else {
				user.heightLevel = rs.getInt(4);
				user.teleportToX = rs.getInt(5);
				user.teleportToY = rs.getInt(6);
				user.playerRights = rs.getInt(7);
				user.playerIsMember = rs.getInt(8);
				user.playerMessages = rs.getInt(9);
				user.playerLastConnect = rs.getString(10);
				user.playerLastLogin = rs.getInt(11);
				user.playerEnergy = rs.getInt(12);
				user.playerLook[0] = rs.getInt(13);
				user.playerLook[1] = rs.getInt(14);
				user.playerLook[2] = rs.getInt(15);
				user.playerLook[3] = rs.getInt(16);
				user.playerLook[4] = rs.getInt(17);
				user.playerLook[5] = rs.getInt(18);

				for (int i = 0; i < 25; i++) {
					user.playerXP[i] = rs.getInt(i + 19);
					user.playerLevel[i] = rs.getInt(i + 44);
				}

				stmt = conn.createStatement();
				rs = stmt
						.executeQuery("SELECT * FROM equipment WHERE username='"
								+ user.playerName + "'");

				if (!rs.next())
					return 3;

				for (int i = 0; i < 14; i++) {
					user.playerEquipment[i] = rs.getInt(i + 2);
					user.playerEquipmentN[i] = rs.getInt(i + 16);
				}

				stmt = conn.createStatement();
				rs = stmt
						.executeQuery("SELECT * FROM charitems WHERE username='"
								+ user.playerName + "'");
				while (rs.next()) {
					user.playerItems[rs.getInt(2)] = rs.getInt(3);
					user.playerItemsN[rs.getInt(2)] = rs.getInt(4);
				}

				stmt = conn.createStatement();
				rs = stmt
						.executeQuery("SELECT * FROM bankitems WHERE username='"
								+ user.playerName + "'");
				while (rs.next()) {
					user.bankItems[rs.getInt(2)] = rs.getInt(3);
					user.bankItemsN[rs.getInt(2)] = rs.getInt(4);
				}

				stmt = conn.createStatement();
				rs = stmt
						.executeQuery("SELECT * FROM friendlist WHERE username='"
								+ user.playerName + "'");
				int i = 0;
				while (rs.next()) {
					user.playerFriends[i] = rs.getLong(2);
					i++;
				}

				stmt = conn.createStatement();
				rs = stmt
						.executeQuery("SELECT * FROM ignorelist WHERE username='"
								+ user.playerName + "'");
				i = 0;
				while (rs.next()) {
					user.playerIgnores[i] = rs.getLong(2);
					i++;
				}
			}
			return 1;
		} catch (Exception ex) {
			ex.printStackTrace();
			return 3;
		}
	}

	public int readCharacter(RSPlayer user) {
		if (server.playerHandler.updateRunning) {
			user.returnCode = 14;
			misc.println(user.playerName + " refused - update is running !");
			return 3;
		} else if (server.playerHandler.isPlayerOn(user.playerName)) {
			user.returnCode = 5;
			user.destroy();
			user.savefile = false;
			misc.println(user.playerName + " is already online.");
			return 3;
		} else {
			int LoadGame = loadCharacter(user);
			if (LoadGame == 2) { // Wrong password
				user.returnCode = 3;
				return 3;
			} else if (LoadGame == 3) {// wrong username
				user.returnCode = 3;
				return 3;
			}
		}
		return 1;
		// stmt = conn.createStatement();
		// rs = stmt.executeQuery("SELECT * FROM characters WHERE
		// username='"+user.playerName+"'");
	}

	public int loadCharacter(RSPlayer user) {
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM characters WHERE username='"
					+ user.playerName + "'");
			if (!rs.next())
				return 3;
			else if (!rs.getString(3).equalsIgnoreCase(user.playerPass))
				return 2;
			else {
				user.location[2] = rs.getInt(4);
				user.teleportToX = rs.getInt(5);
				user.teleportToY = rs.getInt(6);
				user.playerRights = rs.getInt(7);
				user.playerIsMember = rs.getInt(8);
				user.playerMessages = rs.getInt(9);
				user.playerLastConnect = rs.getString(10);
				user.playerLastLogin = rs.getInt(11);
				user.playerEnergy = rs.getInt(12);
				user.playerColor[0] = rs.getInt(13);
				user.playerColor[1] = rs.getInt(14);
				user.playerColor[2] = rs.getInt(15);
				user.playerColor[3] = rs.getInt(16);
				user.playerColor[4] = rs.getInt(17);
				user.playerColor[5] = rs.getInt(18);
				user.playerDesigned = rs.getInt(19);

				for (int i = 0; i < 25; i++) {
					user.playerXP[i] = rs.getInt(i + 20);
					user.playerLevel[i] = rs.getInt(i + 45);
				}
				
				

				stmt = conn.createStatement();
				rs = stmt
						.executeQuery("SELECT * FROM equipment WHERE username='"
								+ user.playerName + "'");

				if (!rs.next())
					return 3;

				for (int i = 0; i < 14; i++) {
					user.playerEquipment[i] = rs.getInt(i + 2);
					user.playerEquipmentN[i] = rs.getInt(i + 16);
				}

				stmt = conn.createStatement();
				rs = stmt
						.executeQuery("SELECT * FROM charitems WHERE username='"
								+ user.playerName + "'");
				while (rs.next()) {
					user.playerItems[rs.getInt(2)] = rs.getInt(3);
					user.playerItemsN[rs.getInt(2)] = rs.getInt(4);
				}

				stmt = conn.createStatement();
				rs = stmt
						.executeQuery("SELECT * FROM bankitems WHERE username='"
								+ user.playerName + "'");
				while (rs.next()) {
					user.bankItems[rs.getInt(2)] = rs.getInt(3);
					user.bankItemsN[rs.getInt(2)] = rs.getInt(4);
				}

				stmt = conn.createStatement();
				rs = stmt
						.executeQuery("SELECT * FROM friendlist WHERE username='"
								+ user.playerName + "'");
				int i = 0;
				while (rs.next()) {
					user.playerFriends[i] = rs.getLong(2);
					i++;
				}

				stmt = conn.createStatement();
				rs = stmt
						.executeQuery("SELECT * FROM ignorelist WHERE username='"
								+ user.playerName + "'");
				i = 0;
				while (rs.next()) {
					user.playerIgnores[i] = rs.getLong(2);
					i++;
				}
			}
			return 1;
		} catch (SQLException ex) {
			ex.printStackTrace();
			return 3;
		}
	}

	public static boolean saveCharacter(RSPlayer user) {
		try {
			int skillxpTotal = 0;
			int levelTotal = 0;
			String exec = "UPDATE characters SET password='" + user.playerPass
	        + "'," + "height='" + user.location[2] + "', posx='"
	        + user.teleportToX + "', posy='" + user.teleportToY
	        + "', rights='" + user.playerRights + "', ismember='"
	        + user.playerIsMember + "', messages='"
	        + user.playerMessages + "', lastconnection='"
	        + user.playerLastConnect + "', energy='" + user.playerEnergy
	        + "', playerdesing='"+user.playerDesigned;

			for (int i = 0; i <= 20; i++) {
				skillxpTotal += user.playerXP[i];
				exec += ", skillxp" + i + "='" + user.playerXP[i] + "'";
			}

			for (int i = 0; i <= 20; i++) {
				levelTotal += user.playerLevel[i];
				exec += ", level" + i + "='" + user.playerLevel[i] + "'";
			}

			exec += ", skillxpTotal='" + skillxpTotal + "', levelTotal='"
					+ levelTotal + "' WHERE username='" + user.playerName + "'";
			stmt.executeUpdate(exec);

			// exec="DELETE FROM equipment WHERE
			// username='"+user.playerName+"'";
			// stmt.executeUpdate(exec);
			exec = "UPDATE equipment SET equip0='" + user.playerEquipment[0]
					+ "'";// equipment

			for (int i = 1; i <= 13; i++)
				exec += ", equip" + i + "='" + user.playerEquipment[i] + "'";
			for (int i = 0; i <= 13; i++)
				exec += ", equipN" + i + "='" + user.playerEquipmentN[i] + "'";
			exec += " WHERE username='" + user.playerName + "'";
			stmt.executeUpdate(exec);

			// charitems
			exec = "DELETE FROM charitems WHERE username='" + user.playerName
					+ "'";
			stmt.executeUpdate(exec);

			for (int i = 0; i < user.playerItems.length; i++) {
				if (user.playerItems[i] > 0) {
					exec = "INSERT INTO charitems VALUES ('" + user.playerName
							+ "', " + i + ", " + user.playerItems[i] + ", "
							+ user.playerItemsN[i] + ")";
					stmt.executeUpdate(exec);
				}
			}

			// bankitems
			exec = "DELETE FROM bankitems WHERE username='" + user.playerName
					+ "'";
			stmt.executeUpdate(exec);

			for (int i = 0; i < user.bankItems.length; i++) {
				if (user.bankItems[i] > 0) {
					exec = "INSERT INTO bankitems VALUES ('" + user.playerName
							+ "', " + i + ", " + user.bankItems[i] + ", "
							+ user.bankItemsN[i] + ")";
					stmt.executeUpdate(exec);
				}
			}

			// friends
			exec = "DELETE FROM friendlist WHERE username='" + user.playerName
					+ "'";
			stmt.executeUpdate(exec);

			for (int i = 0; i < user.playerFriends.length; i++) {
				if (user.playerFriends[i] > 0) {
					exec = "INSERT INTO friendlist VALUES ('" + user.playerName
							+ "', '" + user.playerFriends[i] + "')";
					// misc.println(exec);
					stmt.executeUpdate(exec);
				}
			}

			// ignores
			exec = "DELETE FROM ignorelist WHERE username='" + user.playerName
					+ "'";
			stmt.executeUpdate(exec);

			for (int i = 0; i < user.playerIgnores.length; i++) {
				if (user.playerIgnores[i] > 0) {
					exec = "INSERT INTO ignorelist VALUES ('" + user.playerName
							+ "', " + user.playerIgnores[i] + ")";
					stmt.executeUpdate(exec);
				}
			}
			return true;
		} catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}

	}

	public boolean saveMoreInfo(Player user) {
		try {
			String exec = "";
			exec = "UPDATE moreinfo SET ancients='" + user.ancients + "', "
					+ "questpoints='" + user.totalqp + "', head='" + user.pHead
					+ "', torso='" + user.pTorso + "', " + "arms='"
					+ user.pArms + "', hands='" + user.pHands + "', legs='"
					+ user.pLegs + "', feet='" + user.pFeet + "', " + "beard='"
					+ user.pBeard + "', mime1='" + user.Mime1 + "', mime2='"
					+ user.Mime2 + "', mime3='" + user.Mime3 + "', "
					+ "mime4='" + user.Mime4 + "', specialattack='"
					+ user.specialEnergy + "' WHERE username='"
					+ user.playerName + "'";
			stmt.executeUpdate(exec);
			return true;
		} catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}
	}

	public boolean loadMoreInfo(Player user) {
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM moreinfo WHERE username='"
					+ user.playerName + "'");

			if (!rs.next())
				return false;

			user.ancients = rs.getInt(2);

			user.totalqp = rs.getInt(3);

			user.pHead = rs.getInt(4);
			user.pTorso = rs.getInt(5);
			user.pArms = rs.getInt(6);
			user.pHands = rs.getInt(7);
			user.pLegs = rs.getInt(8);
			user.pFeet = rs.getInt(9);
			user.pBeard = rs.getInt(10);

			user.Mime1 = rs.getInt(11);
			user.Mime2 = rs.getInt(12);
			user.Mime3 = rs.getInt(13);
			user.Mime4 = rs.getInt(14);

			user.specialEnergy = rs.getInt(15);

			return true;
		} catch (SQLException ex) {
			ex.printStackTrace();
			return false;
		}
	}
	
	public boolean loadMoreInfo(RSPlayer user) {
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM moreinfo WHERE username='"
					+ user.playerName + "'");

			if (!rs.next())
				return false;

			user.ancients = rs.getInt(2);

			user.totalQP = rs.getInt(3);

			user.pHead = rs.getInt(4);
			user.pTorso = rs.getInt(5);
			user.pArms = rs.getInt(6);
			user.pHands = rs.getInt(7);
			user.pLegs = rs.getInt(8);
			user.pFeet = rs.getInt(9);
			user.pBeard = rs.getInt(10);

			user.emotes[0] = rs.getInt(11);
			user.emotes[1] = rs.getInt(12);
			user.emotes[2] = rs.getInt(13);
			user.emotes[3] = rs.getInt(14);

			user.specialEnergy = rs.getInt(15);
			return true;
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		}
	}

	public static void loadVarInt(vars vv, String pname) {
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM varInt WHERE username='"
					+ pname + "'");

			vv.currVarInt = 0;

			while (rs.next()) {
				misc.println("Load Var Int: " + vv.currVarInt);
				vv.nameVarInt[vv.currVarInt] = rs.getString(2);
				vv.varInt[vv.currVarInt] = rs.getInt(3);
				vv.currVarInt++;
				if (vv.currVarInt == vv.maxVarInt) {
					misc
							.println("Pastaba! Ne visi variables uzkrauti del limit exceed.");
					break;
				}
			}
		} catch (Exception ex) {
			misc.println("Exceptionas kraunant variables (int)!");
			ex.printStackTrace();
		}
	}

	public void loadVarStr(vars vv, String pname) {
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM varStr WHERE username='"
					+ pname + "'");

			vv.currVarStr = 0;

			while (rs.next()) {
				vv.nameVarStr[vv.currVarStr] = rs.getString(2);
				vv.varStr[vv.currVarStr] = rs.getString(3);
				vv.currVarStr++;
				if (vv.currVarStr == vv.maxVarStr) {
					misc
							.println("Pastaba! Ne visi variables uzkrauti del limit exceed (string).");
					break;
				}
			}
		} catch (Exception ex) {
			misc.println("Exceptionas kraunant variables (str)!");
			ex.printStackTrace();
		}
	}

	public static void saveVars(vars vv, String pname) {
		try {
			String exec = "DELETE FROM varint WHERE username='" + pname + "'";
			stmt.executeUpdate(exec);

			exec = "DELETE FROM varstr WHERE username='" + pname + "'";
			stmt.executeUpdate(exec);

			for (int i = 0; i < vv.currVarInt; i++) {
				stmt.executeUpdate("INSERT INTO varint VALUES ('" + pname
						+ "', '" + vv.nameVarInt[i] + "', " + vv.varInt[i]
						+ ")");
			}

			for (int i = 0; i < vv.currVarStr; i++) {
				stmt.executeUpdate("INSERT INTO varstr VALUES ('" + pname
						+ "', '" + vv.nameVarStr[i] + "', '" + vv.varStr[i]
						+ "')");
			}
		} catch (SQLException ex) {
			misc.println("Error saving player vars.");
			ex.printStackTrace();
		}
	}
}